// backend/index.js

const express = require("express");
const sql = require("mssql");
const cors = require("cors");
const { formatDate } = require("./utils");

const app = express();
app.use(cors());
const port = 3000;

// SQL Server config
const dbConfig = {
  user: "sa",
  password: "Sa123456", //SH: yyt123456.. | SZ: Sa123456
  server: "localhost", // 192.168.1.114
  database: "EETownSH3New", //SH: EETownSH3 | SZ: EETownSH3New
  options: {
    encrypt: false, // 本地 SQL Server 设置为 false
    trustServerCertificate: true,
  },
};

//分页获取包裹信息getScanData
app.get("/api/scan-data", async (req, res) => {
  try {
    // 获取分页参数，默认 page=1, size=10
    const page = parseInt(req.query.page) || 1;
    const size = parseInt(req.query.size) || 10;
    const offset = (page - 1) * size;

    await sql.connect(dbConfig);

    // 按 ScanTime 降序分页查询
    //1. ShangHai
    // const result = await sql.query`
    // SELECT TOP
    //   5 *
    // FROM
    //   TBParcel
    // WHERE
    //   OperationTime IS NOT NULL
    // ORDER BY
    //   OperationTime DESC
    // `;

    //2. ShenZhen
    const result = await sql.query`
    SELECT TOP
      5 * 
    FROM
      TBParcel 
    WHERE
      CreateTime IS NOT NULL 
    ORDER BY
      CreateTime DESC
    `;

    // 格式化时间字段
    //1. ShangHai
    // const formattedData = result.recordset.map((item) => {
    //   return {
    //     ...item,
    //     OperationTime: item.OperationTime
    //       ? formatDate(item.OperationTime)
    //       : null,
    //   };
    // });

    //2. ShenZhen
    const formattedData = result.recordset.map((item) => {
      return {
        ...item,
        CreateTime: item.CreateTime ? formatDate(item.CreateTime) : null,
      };
    });

    // 结果直接返回
    res.json({ data: formattedData });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "数据库错误" });
  }
});

//获取格口数量
app.get("/api/port-count", async (req, res) => {
  try {
    await sql.connect(dbConfig);
    const result = await sql.query`SELECT COUNT(*) as total FROM TBPort`;
    res.json({ count: result.recordset[0].total });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "数据库错误" });
  }
});

// 连接数据库并获取今日包裹数量
app.get("/api/today-count", async (req, res) => {
  try {
    await sql.connect(dbConfig);
    const result = await sql.query`
      SELECT COUNT(*) as total FROM TBParcel`;
    res.json({ count: result.recordset[0].total });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "数据库错误" });
  }
});

//获取今日异常包裹数量，PortID为29表示异常格口
app.get("/api/today-exception-count", async (req, res) => {
  try {
    await sql.connect(dbConfig);
    const result = await sql.query`
      SELECT COUNT(*) as total FROM TBParcel WHERE PortID = 29`;
    res.json({ count: result.recordset[0].total });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "数据库错误" });
  }
});

// 获取累计包裹数量
app.get("/api/total-count", async (req, res) => {
  try {
    await sql.connect(dbConfig);
    const result = await sql.query`SELECT COUNT(*) as total FROM TBParcelBAK`;
    res.json({ count: result.recordset[0].total });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "数据库错误" });
  }
});

// 获取各个分拣口的包裹数量
app.get("/api/port-counts", async (req, res) => {
  try {
    await sql.connect(dbConfig);
    const result = await sql.query`
      SELECT P.Name as portName, COUNT(*) as parcelCount
      FROM TBParcel T
      LEFT JOIN TBPort P ON T.PortID = P.ID
      GROUP BY P.Name
      ORDER BY parcelCount DESC
    `;
    res.json(result.recordset);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "数据库错误" });
  }
});

// 获取今日统计数据聚合接口
app.get("/api/today-stats", async (req, res) => {
  try {
    await sql.connect(dbConfig);

    // 查询今日分拣总数
    const todayCountResult = await sql.query`
      SELECT COUNT(*) as total FROM TBParcel`;

    // 查询今日异常包裹数量 (PortID为17表示异常格口)
    const exceptionCountResult = await sql.query`
      SELECT COUNT(*) as total FROM TBParcel WHERE PortID = 17`;

    // 查询各个分拣口的包裹数量
    //1. ShangHai
    // const portCountsResult = await sql.query`
    //   SELECT T.PortID as portId, P.PortName as portName, COUNT(*) as parcelCount
    //   FROM TBParcel T
    //   LEFT JOIN TBPort P ON T.PortID = P.Code
    //   GROUP BY T.PortID, P.PortName
    //   ORDER BY parcelCount DESC
    // `;

    //2. ShenZhen
    const portCountsResult = await sql.query`
      SELECT T.PortID as portId, P.Name as portName, COUNT(*) as parcelCount
      FROM TBParcel T
      LEFT JOIN TBPort P ON T.PortID = P.Code
      GROUP BY T.PortID, P.Name
      ORDER BY parcelCount DESC
    `;

    res.json({
      todayCount: todayCountResult.recordset[0].total,
      exceptionCount: exceptionCountResult.recordset[0].total,
      portCounts: portCountsResult.recordset,
    });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "数据库错误" });
  }
});

// 获取分拣口包裹数量Top10
app.get("/api/port-top", async (req, res) => {
  try {
    await sql.connect(dbConfig);
    const result = await sql.query`
      SELECT TOP 10 
      PortID,
      COUNT(*) AS Value
      FROM TBParcel
      WHERE PortID IS NOT NULL
      GROUP BY PortID
      ORDER BY Value DESC
    `;
    const data = result.recordset.map((row) => ({
      portId: row.PortID,
      value: row.Value,
    }));

    res.json({ data });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "数据库错误" });
  }
});

app.listen(port, () => {
  console.log(`server服务已启动： http://localhost:${port}`);
});
